const mysql = require('mysql2/promise'); // 使用Promise版本的mysql2

async function main() {
  // 创建数据库连接池
  const pool = mysql.createPool({
    host: 'localhost',
    user: 'root', // 请替换为你的数据库用户名
    password: '123456', // 请替换为你的数据库密码
    database: 'bank-system', // 请替换为你的数据库名
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
  });

  try {
    // 测试数据库连接
    const [rows] = await pool.execute('SELECT 1 + 1 AS result');
    console.log('数据库连接成功:', rows[0].result);

    // 查询所有用户信息
    const [users] = await pool.execute('SELECT * FROM userInfo');
    console.log('\n用户信息:');
    users.forEach(user => {
      console.log(`${user.customerID}: ${user.customerName} - ${user.telephone}`);
    });

    // 查询账户信息并关联用户和存款类型
    const [accounts] = await pool.execute(`
      SELECT 
        c.cardID, 
        u.customerName, 
        d.savingName, 
        c.balance 
      FROM cardInfo c
      JOIN userInfo u ON c.customerID = u.customerID
      JOIN deposit d ON c.savingID = d.savingID
    `);
    console.log('\n账户信息:');
    accounts.forEach(account => {
      console.log(`${account.cardID}: ${account.customerName} - ${account.savingName} - 余额: ${account.balance}`);
    });

    // 示例事务处理：模拟转账操作
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      
      // 转出账户
      const fromCardId = '1010357600000001';
      // 转入账户
      const toCardId = '1010357600000002';
      // 转账金额
      const amount = 100.00;

      // 查询转出账户余额
      const [fromAccount] = await connection.execute(
        'SELECT balance FROM cardInfo WHERE cardID = ?', 
        [fromCardId]
      );
      
      if (fromAccount.length === 0) {
        throw new Error('转出账户不存在');
      }
      
      if (fromAccount[0].balance < amount) {
        throw new Error('余额不足');
      }

      // 更新转出账户余额
      await connection.execute(
        'UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?', 
        [amount, fromCardId]
      );

      // 更新转入账户余额
      await connection.execute(
        'UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?', 
        [amount, toCardId]
      );

      // 记录交易信息
      const tradeDate = new Date();
      await connection.execute(
        'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (?, ?, ?, ?, ?)',
        [tradeDate, '转账', fromCardId, -amount, `转给${toCardId}`]
      );
      
      await connection.execute(
        'INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (?, ?, ?, ?, ?)',
        [tradeDate, '转账', toCardId, amount, `来自${fromCardId}`]
      );

      // 提交事务
      await connection.commit();
      console.log('\n转账成功!');
    } catch (error) {
      // 回滚事务
      await connection.rollback();
      console.error('转账失败:', error.message);
    } finally {
      connection.release();
    }

    // 查询交易记录
    const [trades] = await pool.execute(
      'SELECT * FROM tradeInfo ORDER BY tradeDate DESC LIMIT 5'
    );
    console.log('\n最近交易记录:');
    trades.forEach(trade => {
      console.log(`${trade.tradeDate}: ${trade.cardID} - ${trade.tradeType} - ${trade.tradeMoney}`);
    });

  } catch (error) {
    console.error('数据库操作失败:', error);
  } finally {
    // 关闭连接池
    await pool.end();
    console.log('\n数据库连接已关闭');
  }
}

main();